package cn.ds.general.sql.util;

import cn.ds.general.sql.entity.*;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.json.JSONUtil;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.SelectQuery;
import org.jooq.impl.DefaultDSLContext;
import org.junit.jupiter.api.Test;

import java.util.Arrays;
import java.util.Collections;

import static cn.ds.general.sql.util.SqlUtil.genSql;
import static org.jooq.impl.DSL.table;
import static org.junit.jupiter.api.Assertions.assertEquals;

class SqlUtilTestByMe {

    @Test
    public void test1() {
        Sql innerSql = Sql
                .builder()
                .fields(Arrays.asList(
                        Field.builder().field("name").alia("user_name").build(),
                        Field.builder().field("age").build(),
                        Field.builder().field("gender").build()
                ))
                .table(Table.builder().name("user").alia("u").build())
                .where(Arrays.asList(
                        Condition.builder().field("age").operator(">").values(Collections.singletonList(12)).build(),
                        Condition.builder().field("name").operator("like").values(Collections.singletonList("tom")).build()
                ))
                .limit(Limit.builder().start(0).offset(10).build())
                .build();

        Sql condSql = Sql
                .builder()
                .fields(Arrays.asList(
                        Field.builder().field("name").alia("user_name").build(),
                        Field.builder().field("age").build(),
                        Field.builder().field("gender").build()
                ))
                .table(Table.builder().name("user").alia("u").build())
                .where(Arrays.asList(
                        Condition.builder().field("age").operator(">").values(Collections.singletonList(12)).build(),
                        Condition.builder().field("name").operator("like").values(Collections.singletonList("tom")).build()
                ))
                .limit(Limit.builder().start(0).offset(10).build())
                .build();

        Sql sql = Sql
                .builder()
                .fields(Arrays.asList(
                        Field.builder().field("name").alia("user_name").build(),
                        Field.builder().field("age").build(),
                        Field.builder().caseElse(-1).alia("salary").caseWhens(Arrays.asList(CaseWhen.builder().thenValue("1").type("int").whenConditions(Arrays.asList(Condition.builder().field("age").operator(">").values(Collections.singletonList(12)).build())).build(), CaseWhen.builder().thenValue("2").type("int").whenConditions(Arrays.asList(Condition.builder().field("age").operator("<").values(Collections.singletonList(18)).build())).build())).build(),
                        Field.builder().field("gender").build()
                ))
                .table(Table.builder().sql(innerSql).alia("u").build())
                .joins(Arrays.asList(Join.builder().table(Table.builder().name("dept").build()).on(Arrays.asList(Condition.builder().field("id").operator("=").values(Arrays.asList(1)).build())).build()))
                .where(Arrays.asList(
                        Condition.builder().field("age").operator(">").values(Collections.singletonList(12)).build(),
                        Condition.builder().field("name").operator("in").sql(condSql).build()
                ))
                .groupBy(GroupBy.builder().fields(Arrays.asList(Field.builder().field("name").build())).build())
                .orderBy(Collections.singletonList(OrderBy.builder().name("age").order("desc").build()))
                .limit(Limit.builder().start(0).offset(10).build())
                .build();
        System.out.println(JSONUtil.toJsonStr(sql));
//        System.out.println(SqlUtil.genSql(sql));
    }

    @Test
    public void test2() {
        System.out.println(NumberUtil.isInteger("123"));
        System.out.println(NumberUtil.isNumber("3e12"));
        System.out.println(JSONUtil.isTypeJSON("33"));
    }

    @Test
    public void test3() {
        Sql innerSql = Sql
                .builder()
                .fields(Arrays.asList(
                        Field.builder().field("name").alia("user_name").build(),
                        Field.builder().field("age").build(),
                        Field.builder().field("gender").build(),
                        Field.builder().caseElse(-1).alia("salary").caseWhens(Arrays.asList(CaseWhen.builder().thenValue("1").type("int").whenConditions(Arrays.asList(Condition.builder().field("age").operator(">").values(Collections.singletonList(12)).build())).build(), CaseWhen.builder().thenValue("2").type("int").whenConditions(Arrays.asList(Condition.builder().field("age").operator("<").values(Collections.singletonList(18)).build())).build())).build()
                ))
                .table(Table.builder().name("user").alia("u").build())
                .where(Arrays.asList(
                        Condition.builder().field("age").operator(">").values(Collections.singletonList(12)).build(),
                        Condition.builder().field("name").operator("like").values(Collections.singletonList("tom")).build()
                ))
                .limit(Limit.builder().start(0).offset(10).build())
                .build();
        System.out.println(SqlUtil.genSql(innerSql));
    }

    @Test
    public void testGenSqlWithUnion() {
        Sql sql = Sql
                .builder()
                .fields(Collections.singletonList(Field.builder().field("id").build()))
                .table(Table.builder().name("test_table1").build())
                .union(Union.builder().type("union").build())
                .build();
        Sql sql1 = Sql
                .builder()
                .table(Table.builder().name("test_table2").build())
                .build();
        sql.getUnion().setSqls(Arrays.asList(sql1));

        SelectQuery result = genSql(sql);
        System.out.println(result);
    }

    @Test
    public void testGenSqlWithUnionAll() {
        Sql sql = new Sql();
        sql.setUnion(new cn.ds.general.sql.entity.Union());
        sql.getUnion().setType("union all");
        Sql sql1 = Sql
                .builder()
                .fields(Collections.singletonList(Field.builder().field("id").build()))
                .table(Table.builder().name("test_table1").build())
                .build();
        Sql sql2 = Sql
                .builder()
                .table(Table.builder().name("test_table2").build())
                .build();
        sql.getUnion().setSqls(Arrays.asList(sql1, sql2));

        SelectQuery result = genSql(sql);
        System.out.println(result);
    }

    @Test
    public void testGenSqlWithIntersect() {
        Sql sql = new Sql();
        sql.setUnion(new cn.ds.general.sql.entity.Union());
        sql.getUnion().setType("intersect");
        Sql sql1 = Sql
                .builder()
                .fields(Collections.singletonList(Field.builder().field("id").build()))
                .table(Table.builder().name("test_table1").build())
                .build();
        Sql sql2 = Sql
                .builder()
                .table(Table.builder().name("test_table2").build())
                .build();
        sql.getUnion().setSqls(Arrays.asList(sql1, sql2));

        SelectQuery result = genSql(sql);
        System.out.println(result);
    }

    @Test
    public void testGenSqlWithExcept() {
        Sql sql = new Sql();
        sql.setUnion(new cn.ds.general.sql.entity.Union());
        sql.getUnion().setType("except");
        sql.getUnion().setSqls(Collections.singletonList(new Sql()));

        DSLContext context = new DefaultDSLContext(SQLDialect.DEFAULT);
        SelectQuery unionQuery = context.selectQuery();
        unionQuery.except(genSql(sql.getUnion().getSqls().get(0)));

        SelectQuery result = genSql(sql);
        assertEquals(unionQuery.toString(), result.toString());
    }

    @Test
    public void testGenSqlWithTable() {
        Sql sql = new Sql();
        sql.setTable(new cn.ds.general.sql.entity.Table());
        sql.getTable().setName("test_table");

        SelectQuery result = genSql(sql);
        System.out.println(result);
    }

    @Test
    public void testGenSqlWithTableAlia() {
        Sql sql = new Sql();
        sql.setTable(new cn.ds.general.sql.entity.Table());
        sql.getTable().setName("test_table");
        sql.getTable().setAlia("t");

        DSLContext context = new DefaultDSLContext(SQLDialect.DEFAULT);
        SelectQuery selectQuery = context.selectQuery();
        selectQuery.addFrom(table("test_table").as("t"));

        SelectQuery result = genSql(sql);
        assertEquals(selectQuery.toString(), result.toString());
    }

    @Test
    public void testGenSqlWithFields() {
        Sql sql = new Sql();
        sql.setFields(Collections.singletonList(new cn.ds.general.sql.entity.Field()));
        sql.getFields().get(0).setField("test_field");
        sql.setTable(Table.builder().name("test_table").build());


        SelectQuery result = genSql(sql);
        System.out.println(result);
    }

}